IF OBJECT_ID('ssf.ptx_parse_task_job_name') IS NOT NULL
	DROP FUNCTION ssf.ptx_parse_task_job_name
GO

CREATE FUNCTION ssf.ptx_parse_task_job_name (
	@job_name NVARCHAR(128)
)
RETURNS TABLE
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

	RETURN (
		WITH parsed
		AS (
			SELECT 1 AS position
				, SUBSTRING(@job_name, CHARINDEX('_', @job_name) + 1, LEN(@job_name)) AS string
				, SUBSTRING(@job_name, 1, CHARINDEX('_', @job_name) -1) AS element
			WHERE @job_name LIKE N'xt\__%\__%\__%\__%' ESCAPE '\'
			UNION ALL
			SELECT position + 1
				, CASE WHEN CHARINDEX('_', string) > 0
					THEN SUBSTRING(string, CHARINDEX('_', string) + 1, LEN(string))
					ELSE ''
					END
				, CASE WHEN CHARINDEX('_', string) > 0
					THEN SUBSTRING(string, 1, CHARINDEX('_', string) -1)
					ELSE string
					END
			FROM parsed
			WHERE string <> ''
		)
		SELECT CAST([2] AS INT) AS task_group_id
			, CAST([3] AS INT) AS task_id
			, STUFF(STUFF(SUBSTRING([4], 1, 8), 5, 0, '-'), 8, 0, '-')
				+ SPACE(1)
				+ STUFF(STUFF(SUBSTRING([4], 9, 6), 3, 0, ':'), 6, 0, ':') AS task_group_run_date
			, CAST([5] AS INT) AS task_group_spid
		FROM (
			SELECT position, element
			FROM parsed
		) AS parsed_rows
		PIVOT (
			MAX(element)
			FOR position IN ([2], [3], [4], [5])
		) AS parsed_columns
	)
GO
